-- genre_info
insert into genre_info values(1,'유머/코미디');
insert into genre_info values(2,'멜로/드라마');
insert into genre_info values(3,'스릴러');
insert into genre_info values(4,'액션');
insert into genre_info values(5,'SF');
insert into genre_info values(6,'에로');
insert into genre_info values(7,'호러/공포');
insert into genre_info values(8,'애니메이션');
insert into genre_info values(9,'다큐멘터리');

--country_info
insert into country_info values(0,'국가불명');
insert into country_info values(1,'그리스');
insert into country_info values(2,'독일');
insert into country_info values(3,'미국');
insert into country_info values(4,'스페인');
insert into country_info values(5,'영국');
insert into country_info values(6,'이탈리아');
insert into country_info values(7,'인도');
insert into country_info values(8,'일본');
insert into country_info values(9,'중국');
insert into country_info values(10,'캐나다');
insert into country_info values(11,'태국');
insert into country_info values(12,'프랑스');
insert into country_info values(13,'한국');
insert into country_info values(14,'홍콩');

--local_info
insert into local_info values(1,'서울');
insert into local_info values(2,'경기');
insert into local_info values(3,'인천');

--movie_grade_info
insert into MOVIE_GRADE_INFO values(1,0);
insert into MOVIE_GRADE_INFO values(2,12);
insert into MOVIE_GRADE_INFO values(3,15);
insert into MOVIE_GRADE_INFO values(4,18);

--payment_method_info
insert into payment_method_info values(1,'신용카드');
insert into payment_method_info values(2,'계좌이체');

--member_level
insert into member_level_info values(1,'manager');
insert into member_level_info values(0,'member');

--member
insert into member values(member_no_seq.nextval,'0000002839482','java','1234','아이유','노원','iu90@naver.com',sysdate,'010',0);
insert into member values(member_no_seq.nextval,'0000001280392','king','1234','보아','강남','boa87@naver.com',sysdate,'011',0);
insert into member values(member_no_seq.nextval,'0000001938203','ibatis','1234','김희선','인천','kim85@naver.com',sysdate,'011',0);
insert into member values(member_no_seq.nextval,'0000001938203','admin','1234','관리자','인천','kim85@naver.com',sysdate,'011',1);

--theater
insert into theater values(theater_no_seq.nextval,'삼성코엑스관',1,'서울특별시 강남구 삼성1동','1544-0000');

-- play room : 영화룸대표번호,영화관번호, 상영관번호,좌석수 
insert into PLAY_ROOM values(play_room_uniq_no_seq.nextval,1,1,50);
select * from statics
--reservation
--예약번호,시간표번호,결제방식번호,회원번호,예매매수,좌석번호,예매날,가격
insert into reservation values(res_no_seq.nextval,5,1,1,2,'A01,A02',sysdate,18000);
insert into reservation values(res_no_seq.nextval,5,1,2,2,'B01,B02',sysdate,18000);
insert into reservation values(res_no_seq.nextval,5,1,3,2,'C01,C02',sysdate,18000);
insert into reservation values(res_no_seq.nextval,5,1,1,2,'A01,A02',sysdate,18000);
select * from TIME_TABLE
--select * 
select * from SCREENING_MOVIE;
select * from genre_info;
select * from MEMBER_LEVEL_INFO;
select * from LOCAL_INFO;
select * from movie;
select * from theater;
select * from play_room;
select * from MOVIE_GRADE_INFO;
select * from time_table;
select * from member;
select * from reservation;
select * from statics;
select * from country_info;

--drop table
drop table screening_movie;
drop table board;
drop table mymovie_info;
drop table reservation;
drop table time_table;
drop table play_room;
drop table theater;
drop table local_info;
drop table statics;
drop table movie;
drop table genre_info;
drop table country_info;
drop table movie_grade_info;
drop table member;
drop table payment_method_info;
drop table member_level_info;

--drop sequence
drop sequence movie_no_seq;
drop sequence member_no_seq;
drop sequence theater_no_seq;
drop sequence play_room_uniq_no_seq;
drop sequence time_table_seq;
drop sequence res_no_seq;
drop sequence board_no_seq;
drop sequence MyMovie_no_seq;

--예매정보 출력
select mem.name, mv.movie_title, r.ticket_count, r.seat_no , tt.theater, pr.play_room_no
from reservation r, movie mv, member mem, payment_method_info p, time_table t, theater tt, play_room pr 
where r.payment_no=p.payment_no and r.member_no=mem.member_no and t.movie_no=mv.movie_no and tt.theater_no=pr.theater_no and pr.play_room_info_no=t.play_room_info_no and r.res_no=2;

--영화 선택시 해당 영화가 상영중인 영화관 뽑아오기
select th.theater, mv.movie_title
from MOVIE mv, screening_movie sm, theater th
where mv.movie_no=sm.movie_no and sm.theater_no=th.theater_no and mv.movie_no=2;

--영화관 선택시 해당 영화 뽑아 오기 
select genre_no,genre from GENRE_INFO;
-- 공식적인 개봉일
select to_char(opening_day,'yyyy-mm-dd') as opening_day from movie where movie_no=1;
--스크리닝 무비에 등록 된 친구들 데려오기
select l.loc_no,l.loc,sm.theater_no,th.theater, sm.movie_no,m.movie_title,to_char(sm.starting_day,'yyyy-mm-dd'),to_char(sm.ending_day,'yyyy-mm-dd')
from screening_movie sm, THEATER th,LOCAL_INFO l,Movie m
where sm.theater_no=th.theater_no and th.loc_no=l.loc_no and sm.movie_no=m.movie_no order by m.movie_no desc
--잡다
select movie_no,movie_title,opening_day from movie order by movie_no desc

		select t.theater_no, t.theater
		from theater t, local_info li
		where t.loc_no=li.loc_no
		and	li.loc_no=1
-- 영화 선택->지역선택-> 극장은 영화가 상영중인 선택 지역의 극장만... 
select  t.theater_no, t.theater
from movie mv, theater t, local_info li, screening_movie smv
where t.loc_no=li.loc_no and mv.movie_no=smv.movie_no and t.theater_no=smv.theater_no
			and mv.movie_no=1 and t.loc_no=1

			
			select  t.theater_no, t.theater
		from movie mv, theater t, local_info li, screening_movie smv
		where t.loc_no=li.loc_no and mv.movie_no=smv.movie_no and t.theater_no=smv.theater_no
					and mv.movie_no=1 and t.loc_no=1
					
delete from RESERVATION					
select res_no, time_table_no, payment_no, member_no,ticket_count,seat_no,to_char(booking_date,'yyyy-mm-dd hh24:mi'), price
		from reservation 
		where time_table_no=1
					and member_no=1		
					
--movie table 에서 movie_no, starting_day, ending_day 추출하여 statics table에 저장
insert into STATICS(1,) value()
--현재 상영작 영화 번호 
select movie_no from movie where sysdate>opening_day and ending_day>sysdate order by movie_no desc
--상영 예정작 영화 번호 
select movie_no from movie where opening_day>sysdate order by mv.movie_no asc
select * from RESERVATION

--reservation 에서 movie_no랑 count
select distinct(m.movie_no), r.ticket_count
from TIME_TABLE tt, RESERVATION r,movie m
where tt.time_table_no=r.time_table_no and tt.movie_no=m.movie_no

select * from reservation
--movie_no에 해당하는 

--bb
select ticket_count from RESERVATION where time_table_no=(select distinct(tt.time_table_no)
from TIME_TABLE tt, RESERVATION r,movie m
where tt.time_table_no=r.time_table_no and tt.movie_no=m.movie_no)
select * from reservation --6개
--boxoffice 등록을 위한 정보 추출
select movie_no, gender, sum(cnt) ticket_count,to_date(opening_day,'yyyy-mm-dd') opening_day,to_date(ending_day,'yyyy-mm-dd') ending_day
from(select SUBSTR(mem.ssn, 7, 1) gender, mv.movie_no movie_no, res.time_table_no, res.ticket_count cnt,mv.opening_day opening_day,mv.ending_day ending_day
   from MEMBER mem, MOVIE mv, RESERVATION res, TIME_TABLE tt
   where mem.member_no=res.member_no 
   and res.time_table_no=tt.time_table_no 
   and mv.movie_no=tt.movie_no
   and sysdate>tt.ending_time)
group by gender,movie_no,opening_day,ending_day 
--
select * from statics
--statics update
update STATICS set total_member=#value# where movie_no=#value# and gender=#value#
-- boxoffice 합산
select movie_no, sum(total_member)
from STATICS group by movie_no
select * from STATICS
--delete
delete SCREENING_MOVIE where theater_no=2 and movie_no=2
--boxoffice 순위별 뽑기
	select movie_no,movie_title,total_member,rk
		from(select RANK() OVER (order by total_member desc) as rk, movie_no,movie_title,total_member
		from(select s.movie_no movie_no, mv.movie_title movie_title,sum(s.total_member)  total_member
				from statics s, movie mv where s.movie_no=mv.movie_no and s.starting_day<sysdate and s.ending_day>sysdate
				group by s.movie_no,mv.movie_title)) where 6>rk order by rk asc

--영화검색 제목으로
		select
		movie_no,movie_title,to_char(opening_day,'yyyy-mm-dd') as opening_day from movie where movie_title='예정작'order by movie_no desc
select movie_title, genre_no from movie
-- 장르별 검색
select mv.movie_no,mv.movie_title,to_char(mv.opening_day,'yyyy-mm-dd') as opening_day 
from movie mv, GENRE_INFO g 
where g.genre_no=mv.genre_no and g.genre='에로' order by mv.movie_no desc
select * from reservation
--박스오피스 정보
select movie_no,movie_title,genre,country,running_time,director,actor,movie_grade_no,opening_day,total_member,rk
		from(select RANK() OVER (order by total_member desc) as rk, movie_no,movie_title,genre,country,running_time,director,actor,movie_grade_no,opening_day,total_member
		from(select s.movie_no movie_no, mv.movie_title movie_title, g.genre genre, c.country country,mv.running_time running_time, mv.director director,mv.actor actor,mg.movie_grade_no movie_grade_no, mv.opening_day opening_day,sum(s.total_member)  total_member
				from statics s, movie mv,MOVIE_GRADE_INFO mg, country_info c,genre_info g where mv.genre_no=g.genre_no and c.country_no=mv.country_no and mg.movie_grade_no=mv.movie_grade_no and s.movie_no=mv.movie_no and sysdate>s.starting_day and s.ending_day>sysdate
				group by s.movie_no,mv.movie_title,g.genre,c.country,mv.running_time,mv.director,mv.actor,mg.movie_grade_no,mv.opening_day)) where 6>rk order by rk asc

--
select mv.movie_no movie_no,mv.movie_title movie_title, mv.genre_no genre_no,g.genre genre, mv.running_time running_time
		from movie mv, COUNTRY_INFO c,genre_info g
		where c.country_no=mv.country_no and g.genre_no=mv.genre_no and mv.movie_title like '%어 벤져스%';		
			
				